import json
import os
import shutil

import facade
from xjlibrary.mdatetime.mtime import getTodayDate
from xjlibrary.mdatetime.mtime2 import MDateTimeUtils
from xjlibrary.our_file_dir import BaseDir

from spie.id_encode import GetLngid

curPath = BaseDir.get_file_dir_absolute(__file__)
configfile = BaseDir.get_new_path(curPath, "db.ini")


class ParaA(object):
    def __init__(self):
        self.mysqlutils = facade.MysqlUtiles(configfile,
                                             "db",
                                             logger=facade.get_streamlogger())
        self.dbsqlutils = facade.Sqlite3Utiles(logger=facade.get_streamlogger()).Sqlite3DBConnectFromFilePath(
            "./db3_template/spiebook.db3", encoding="utf-8")

    def get_date(self):
        sql = "select `json` from books where stat=0"
        rows = self.mysqlutils.SelectFromDB(sql)
        listpara = []
        for row in rows:
            dicts = json.loads(row[0])
            # 不在使用原始id作为rawid s使用doi作为rawid
            # rawid = dicts["rowid"]
            rawid = dicts["DOI"]
            sub_db_id = "00070"
            lngid = GetLngid(sub_db_id, rawid)
            product = "SPIE"
            sub_db = "TS"
            provider = "SPIE"
            down_date = MDateTimeUtils.get_today_date_strings()
            batch = MDateTimeUtils.datetime_to_string(MDateTimeUtils.get_now_datetime(), '%Y%m%d_%H%M%S')
            doi = dicts["DOI"]
            Publisher = dicts["PublisherName"]
            source_type = "1"
            provider_url = "https://www.spiedigitallibrary.org" + dicts["herf"]
            title = dicts["Title"]
            keyword = ";".join(dicts["Keywords"])
            sql = "select `subject` from bookssubject where `books`='{}'".format(dicts["rowid"])
            rows = self.mysqlutils.SelectFromDB(sql)
            listsubject = []
            for row in rows:
                listsubject.append(row[0])
            subject = ";".join(listsubject)
            subject = subject.replace("_", " ")
            abstract = dicts["Abstract"].strip()
            pub_date = dicts["PublicationDateTime"].split("T")[0].replace("-", "")
            AuthorEditorLinks = dicts["AuthorEditorLinks"]
            authorlist = AuthorEditorLinks.split(" ")
            author = ""
            author_id = ""
            for authors in authorlist:
                authorandid = authors.split("|")
                author = author + authorandid[0] + ";"
                if not authorandid[1]:
                    authorandid[1] = authorandid[0]
                author_id = author_id + authorandid[1] + "@" + authorandid[0] + ";"
            author = author.strip(";")
            author_id = author_id.strip(";")
            pub_year = pub_date[:4]
            if dicts["ISBN_PDF"]:
                isbn_pdf = dicts["ISBN_PDF"]
            else:
                isbn_pdf = ""
            if dicts["ISBN_Kindle"]:
                isbn_kindle = dicts["ISBN_Kindle"]
            else:
                isbn_kindle = ""
            if dicts["ISBN_ePub"]:
                isbn_epub = dicts["ISBN_ePub"]
            else:
                isbn_epub = ""
            isbn = isbn_epub
            eisbn = isbn_pdf + ";" + isbn_kindle
            eisbn = eisbn.replace(";;", ";")
            eisbn = eisbn.strip(";")
            cover_path = "/smartlib/sipebook/{}.jpg".format(doi.replace("/", "_").lower())
            country = "US"
            language = "EN"
            listpara.append((lngid, rawid, sub_db_id, product, sub_db, provider, down_date, batch, doi, source_type,
                             provider_url, title, keyword, subject, abstract, pub_date, author_id, author, pub_year,
                             eisbn, isbn, cover_path, country, language,Publisher))

        sql = "insert or ignore into base_obj_meta_a (`lngid`,`rawid`,`sub_db_id`,`product`,`sub_db`,`provider`,`down_date`,`batch`,`doi`,`source_type`,`provider_url`,`title`,`keyword`,`subject`,`abstract`,`pub_date`,`author_id`,`author`,`pub_year`,`eisbn`,`isbn`,`cover_path`,`country`,`language`,`publisher`) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
        self.dbsqlutils.ExeSqlliteMany(sql, listpara)

    def copyTempDB3(self, temp_path, copy_out):
        """
        拷贝创建新的db3文件
        :param temp_path: 模板db3文件路径
        :param copy_out: 新的db3输出路径
        :return: 新db3路径
        """
        today_str = str(getTodayDate())
        if not os.path.isfile(temp_path) or not os.path.isdir(copy_out):
            raise FileNotFoundError
        new_db3_name = 'spiebook_' + today_str + '.db3'
        new_db3_path = os.path.join(copy_out, new_db3_name)
        shutil.copyfile(temp_path, new_db3_path)
        return new_db3_path

    def fromatozt(self):
        """
        从a表提取数据到zt表
        :return:
        """
        temp_db3 = os.path.join(curPath, "db3", "zt_template.db3")
        new_db3_out = os.path.join(curPath, "newDB3")
        BaseDir.create_dir(new_db3_out)
        new_db3_path = self.copyTempDB3(temp_db3, new_db3_out)
        self.db3_zt = facade.Sqlite3Utiles(logger=facade.get_streamlogger()).Sqlite3DBConnectFromFilePath(
            new_db3_path, encoding="utf-8")
        sql = "select * from `base_obj_meta_a` where batch > '{}'".format(
            MDateTimeUtils.datetime_to_string(MDateTimeUtils.get_now_datetime(), '%Y%m%d_000000'))
        rows = self.dbsqlutils.SelectFromSqlliteFetchall_dicts(sql)
        print(rows)
        Listpara = []
        for row in rows:
            print("************************")
            print(row["lngid"])
            bacth = MDateTimeUtils.get_today_date_strings()+"00"
            Listpara.append(
                (row["lngid"], row["rawid"], row['doi'], row['title'], row['eisbn'], row['author'], row['abstract'],
                 row['keyword'], row['pub_date'], row['pub_year'], row['language'], row['country'], "1", "2",
                 bacth, row['cover_path'], "spiebook@" + row["doi"], "spiebook@" + row["provider_url"],
                 "spiebook", row["subject"], row['isbn'],row['publisher']))
        print(Listpara)
        sql = "insert or ignore into modify_title_info_zt (`lngid`,`rawid`,`identifier_doi`,`title`,`identifier_eisbn`,`creator`,`description`,`subject`,`date_created`,`date`,`language`,`country`,`type`,`medium`,`batch`,`cover`,`provider_id`,`provider_url`,`provider`,`provider_subject`,`identifier_pisbn`,`publisher`) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
        self.db3_zt.ExeSqlliteMany(sql, Listpara)


if __name__ == "__main__":
    para = ParaA()
    para.get_date()
    para.fromatozt()
